<?php
/**************************************************************************
 *
 *   Copyright 2010 American Public Media Group
 *
 *   This file is part of AIR2.
 *
 *   AIR2 is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   AIR2 is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with AIR2.  If not, see <http://www.gnu.org/licenses/>.
 *
 *************************************************************************/

require_once 'AIR2_CRUD_Controller.php';

class Inquiry_Controller extends AIR2_CRUD_Controller {

    public $base_model_name = 'Inquiry';
    public $base_view_name  = 'inquiry';
    public $exposed_relations = array(
        'questions' => 'Question',
        'organizations' => 'ProjectOrg', //uri_name => rel_name (usually model name)
        'activity' => 'ProjectActivity',
        'submissions' => 'SrcResponseSet',
        'project_inquiry' => 'ProjectInquiry',
        'detail' => NULL,
    );
    public $autoload_relations = array(
    );
    public $column_blacklist = array(
    );
    public $column_whitelist = array(
    );
    public $taggable = true;


    /**
     * Add custom relations to queries to this controller
     *
     * @param AIR2_Query $q
     * @param boolean $deeply (optional)
     * @return unknown
     */
    protected function _add_query_load_related(&$q, $deeply=true) {
        if (!$this->rel_uri) {
            $q->leftJoin('a.ProjectInquiry pi');
            $q->leftJoin('pi.Project p');
            $q->leftJoin('p.ProjectOrg po');
            $q->leftJoin('po.Organization o');
        }
        elseif ($this->rel_uri === 'organizations') {
            $q->leftJoin('a.Organization o');
            $q->leftJoin('a.ContactUser u');
            $q->leftJoin('u.UserEmailAddress e WITH e.uem_primary_flag = true');
            $q->leftJoin('u.UserPhoneNumber p WITH p.uph_primary_flag = true');
        }
        elseif ($this->rel_uri === 'submissions') {
            $q->leftJoin('a.Source s');
            $q->leftJoin('a.SrcResponse r');
            $q->leftJoin('r.Question q');
            $q->leftJoin('s.SrcEmail e');
            $q->leftJoin('s.SrcMailAddress');
            $q->leftJoin('s.SrcEmail');
            $q->leftJoin('s.SrcPhoneNumber p');
            $q->leftJoin('s.SrcVita v WITH v.sv_type = "E"');
        }
        elseif ($this->rel_uri === 'activity') {
            $q->leftJoin('a.User');
            $q->leftJoin('a.ActivityMaster');
            $id = $this->_find_base()->inq_id;
            $q->where('(a.pa_xid = ? AND a.pa_ref_type = ?)', array($id, 'I'));
            ProjectActivity::joinRelated($q, 'a');
        }

        return parent::_add_query_load_related($q, $deeply);
    }


    /**
     * Override to add custom update queries
     *
     * @param Doctrine_Record $record (reference) the record object to modify
     * @param associative-array $data   the radix data to update the record with
     */
    protected function _update_record(&$record, $data) {
        if ($this->rel_uri === 'organizations') {
            if (isset($data['Organization']) && isset($data['Organization']['org_uuid'])) {
                $uuid = $data['Organization']['org_uuid'];
                $conn = AIR2_DBManager::get_connection();
                $org_id = $conn->fetchOne('select org_id from organization where org_uuid = ?', array($uuid));
                $record->porg_org_id = $org_id;
                unset($data['Organization']);
            }
            if (isset($data['ContactUser']) && isset($data['ContactUser']['user_uuid'])) {
                $uuid = $data['ContactUser']['user_uuid'];
                $conn = AIR2_DBManager::get_connection();
                $user_id = $conn->fetchOne('select user_id from user where user_uuid = ?', array($uuid));
                $record->porg_contact_user_id = $user_id;
                unset($data['ContactUser']);
            }
            if (!$record->porg_status) $record->porg_status = 'A';
        }

        parent::_update_record($record, $data);
    }


    /**
     * Set the page title
     *
     * @param array   $head
     * @return array
     */
    public function get_html_head($head=array()) {
        if ($this->base_uuid) {
            $name = $this->_find_base()->inq_ext_title;
            $head['title'] = AIR2_SYSTEM_DISP_NAME." - Query: $name";
        }
        return $head;
    }


    /**
     * Custom data and views for html requests
     *
     * @param associative-array $data
     * @param string  $view_name
     * @return string html body
     */
    public function get_html_body($data, $view_name=null) {

        if (!$this->base_uuid) {
            redirect('search/inquiries');
        }
        elseif ($this->rel_uri === 'detail') {
            $data = $this->_html_detail(); //html-only, so $data is empty
            $view_name = 'inquiry_detail';
        }
        elseif ($this->base_uuid && !$this->rel_uri) { // base Inquiry level
            $data = $this->_html_dashboard($data);
        }

        return parent::get_html_body($data, $view_name);
    }



    /**
     * Get data for HTML dashboard
     *
     * @param associative-array $data
     * @return associative-array
     */
    protected function _html_dashboard($data) {
        // activity
        $q = $this->build_query($this->base_uuid, 'activity');
        $q->orderBy('pa_dtim DESC');
        $q->limit(5);

        $activity_data = $this->fetch_data($q, true, true);

        // submissions
        //$q = $this->build_query($this->base_uuid, 'submissions');
        //$q->orderBy('srs_cre_dtim DESC');
        //$q->limit(20);
        //$subm_data = $this->fetch_data($q, true, true);

        // ques_responses
        //$qresp_data = $this->_qresp_data($this->_find_base()->inq_id);

        // questions
        $q = $this->build_query($this->base_uuid, 'questions');
        $q->orderBy('ques_dis_seq ASC');
        $ques_data = $this->fetch_data($q, true, true);

        // stats
        $stats_data = $this->_stats_data($this->_find_base()->inq_id);

        // tags
        $q = $this->airtag->create_tag_query($this->_find_base());
        $q->orderBy('added_dtim ASC');
        $tag_data = $this->airtag->exec_tag_query($q, true, true);

        return array(
            'base_url' => $this->uri_for('inquiry').'/'.$this->base_uuid,
            'inq'  => $data,
            //'subm' => $subm_data,
            //'qresp' => $qresp_data,
            'ques' => $ques_data,
            'stats' => $stats_data,
            'act' => $activity_data,
            'tag' =>  $tag_data,
        );
    }


    /**
     * Get data for detail view
     *
     * @return associative-array
     */
    protected function _html_detail() {
        //project
        $q = $this->build_query($this->base_uuid);
        $inq_data = $this->fetch_data($q);

        // tags
        $q = $this->airtag->create_tag_query($this->_find_base());
        $q->orderBy('added_dtim ASC');
        $tag_data = $this->airtag->exec_tag_query($q, true, true);

        return array(
            'base_url' => $this->uri_for('inquiry').'/'.$this->base_uuid,
            'inq'  => $inq_data,
            'tag' => $tag_data,
        );
    }


    /**
     *
     *
     * @param unknown $inquiry_id
     * @return unknown
     */
    private function _qresp_data($inquiry_id) {
        $conn = AIR2_DBManager::get_connection();

        $sql =
            'SELECT sr.sr_id, inq.inq_id, srs.srs_date, ' .
            'sr.sr_orig_value, ques.ques_uuid, src.src_uuid, ' .
            'src.src_username, src.src_first_name, src.src_middle_initial, src.src_last_name ' .
            'FROM inquiry inq ' .
            'LEFT JOIN src_response_set srs ON srs.srs_inq_id = inq.inq_id ' .
            'LEFT JOIN src_response sr ON sr.sr_srs_id = srs.srs_id ' .
            'LEFT JOIN question ques ON ques.ques_id = sr.sr_ques_id ' .
            'LEFT JOIN source src ON src.src_id = srs.srs_src_id ' .
            'WHERE inq.inq_id = ?';

        $qresp = $conn->fetchAll($sql, array($inquiry_id));

        return array(
            'radix' => $qresp,
            'total' => count($qresp),
            'metaData' => $this->_create_metadata_basic (
                'sr_id',
                array(
                    array('name' => 'sr_id', 'type' => 'int'),
                    array('name' => 'inq_id', 'type' => 'int'),
                    array('name' => 'srs_date', 'type' => 'date', 'dateFormat' => 'Y-m-d H:i:s'),
                    array('name' => 'sr_orig_value', 'type' => 'text'),
                    array('name' => 'ques_uuid', 'type' => 'string', 'length' => 12),
                    array('name' => 'src_uuid', 'type' => 'string', 'length' => 12),
                    array('name' => 'src_username', 'type' => 'string', 'length' => 255),
                    array('name' => 'src_first_name', 'type' => 'string', 'length' => 255),
                    array('name' => 'src_middle_initial', 'type' => 'string', 'length' => 1),
                    array('name' => 'src_last_name', 'type' => 'string', 'length' => 255),
                )
            )
        );
    }


    /**
     *
     *
     * @param unknown $inquiry_id
     * @return unknown
     */
    private function _stats_data($inquiry_id) {
        // Sources and Submissions (stats) panel data
        $conn = AIR2_DBManager::get_connection();
        $sent = 0; //$conn->fetchOne(''); TODO: tie to inquiry_src ( or src_activity ?);
        $recv = $conn->fetchOne('Select count(*) FROM src_response_set WHERE srs_inq_id = ?', array($inquiry_id));
        $num_src = $conn->fetchOne('SELECT COUNT(DISTINCT(srs_src_id)) FROM src_response_set WHERE srs_inq_id = ?', array($inquiry_id));
        $pin_total = $conn->fetchOne('SELECT COUNT(*) FROM source WHERE src_status = \'A\'');

        return array(
            'radix' => array(
                'inq_id' => $inquiry_id,
                'inq_uuid' => $this->base_uuid,
                'SubmissionCount' => $recv,
                'SubmissionRate' => ($sent > 0) ? $recv/$sent : 'N/A',
                'SourceCount' => $num_src,
                'SourceRate' => ($pin_total > 0) ? $num_src/$pin_total : 'N/A',
            ),
            'metaData'=> $this->_create_metadata_basic(
                'inq_id',
                array( // column definitions
                    array('name'=>'inq_id'),
                    array('name'=>'inq_uuid'),
                    array('name'=>'SubmissionCount'),
                    array('name'=>'SubmissionRate'),
                    array('name'=>'SourceCount'),
                    array('name'=>'SourceRate'),
                )
            ),
        );
    }


}
